#!/bin/bash

if [ -n "$1" ] ; then
  data_date=$1
else
  data_date=`date -d "-1 days" +%F`
fi

DIM_PRODUCT_INFO="
WITH
    t1 AS (
        SELECT
            product_id, product_name, category_id, brand_id, user_id, start_id,
            is_favorite, is_cart, price, price_level, zf_flag, status, create_time, update_time
        FROM product_warehouse.ods_product_order
        WHERE dt='${data_date}'
    ),
    t2 AS (
        SELECT
            sku_id, product_id, sku_name, price, original_price, create_time, update_time
        FROM product_warehouse.ods_order_detail
        WHERE dt='${data_date}'
    ),
    t3 AS (
        SELECT
            category_id, category_name, product_id, level, status, create_time, update_time
        FROM product_warehouse.ods_product_category
        WHERE dt='${data_date}'
    ),
    t4 AS (
        SELECT
            brand_id,brand_name,brand_desc,country,status,create_time,update_time
        FROM product_warehouse.ods_product_brand
        WHERE dt = '${data_date}'
    ),
    t5 AS (
        SELECT
            content_id, product_id, content_type, title, view_count, create_time
        FROM product_warehouse.ods_payment_method
        WHERE dt='${data_date}'
    ),
    t6 AS (
        SELECT
            user_id, gender, age, register_time, last_login_time, user_level, create_time,
            province AS user_province, city AS user_city
        FROM product_warehouse.ods_user_info
        WHERE dt='${data_date}'
    )
INSERT OVERWRITE TABLE product_warehouse.dim_product_info PARTITION (dt='${data_date}')
SELECT
    t1.product_id,
    t1.product_name,
    t1.category_id,
    t1.brand_id,
    t1.user_id,
    t1.start_id,
    t1.is_favorite,
    t1.is_cart,
    t1.price AS product_price,
    t1.price_level,
    t1.zf_flag,
    t1.status AS product_status,
    t2.sku_id,
    t2.sku_name,
    t2.price AS sku_price,
    t2.original_price AS sku_original_price,
    t3.category_name,
    t3.level,
    t3.status AS category_status,
    t4.brand_name,
    t4.brand_desc,
    t4.country AS brand_country,
    t4.status AS brand_status,
    t6.gender,
    t6.age,
    t6.register_time,
    t6.last_login_time,
    t6.user_level,
    t5.content_type,
    t5.view_count,
    t1.create_time,
    t1.update_time
FROM t1
LEFT JOIN t2 ON t2.product_id=t1.product_id
LEFT JOIN t3 ON t3.category_id=t1.category_id
LEFT JOIN t4 ON t4.brand_id=t1.brand_id
LEFT JOIN t5 ON t5.product_id=t1.product_id
LEFT JOIN t6 ON t6.user_id=t1.user_id
WHERE content_type IS NOT NULL
AND view_count IS NOT NULL;
"

/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "${DIM_PRODUCT_INFO}"